Technical Field
The present invention relates to database optimization and, more particularly, to optimizing joined tables having a large number of columns.
Description of the Related Art
Databases are used for storage and efficient retrieval of a wide variety of data. In some scenarios, multiple tables may be joined into a single, large table. This can produce tables with very large widths, as each constituent table's columns are added to the final table.
Using a database that has too many columns can result in several difficulties. For example, when there is a large number of columns, the time needed to perform a write operation increases. In addition, many database systems have a storage limit for how much information can be stored in a single row—as the number of columns increases, more information is needed per row.
These circumstances arise in businesses such as banking, where data stores are often poorly designed and are not query-efficient. Their data cannot easily be migrated for reasons of legacy support. Existing solutions store multiple physical views of a logic table called “projections.” A “projection” is a column subset of logic table schema. These use query samples to optimize overlapped projection decisions to guarantee that each query will only refer to one projection. However, these techniques are designed for distributed architectures and provide little benefit to single-machine and legacy row-based database instances. Furthermore, they may need significant data duplication and data migration to outside database instances.